SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*WHERE     (IC.TituloValorado IN (91759, 91758, 91757))
where  ic.titulovalorado in (91757,91758,91759)  




*/
CREATE VIEW dbo.vw_OrdenDeMerito
AS
SELECT DISTINCT 
                      SUBSTRING(CO.Descripcion, 1, 200) AS Convocatoria, CO.idConvocatoria, N.NivelEnsenanza AS Nivel, IC.Nivel AS IdNivel, IC.Modalidad, 
                      m.Descripcion, CL.Descripcion AS Ciclo, IC.Ciclo AS IdCiclo, L.localidad, L.idLocalidad, CG.idCargoGenerico AS IdCargo, CG.Descripcion AS Cargo, 
                      AG.idAsignaturaGenerica AS IdAsignatura, SUBSTRING(AG.Descripcion, 9, 150) AS Asignatura, C.Descripcion AS CategoriaDeTitulo, A.Documento, 
                      A.Nombres AS NombreYApellido, ROUND(IC.Puntaje, 5) AS Puntaje, A.Domicilio AS Direccion, L2.localidad AS ALocalidad, A.Telefono AS TE, 
                      CASE WHEN N.IdNivelEnsenanza IN (2) THEN A.LegajoDeInicial WHEN N.IdNivelEnsenanza IN (3) 
                      THEN A.LegajoDePrimaria WHEN N.IdNivelEnsenanza IN (4) THEN A.LegajoDeMedia END AS Legajo, IC.PromedioTitulo AS Promedio, 
                      IC.TituloEspecifico, IC.SituacionRevista, CASE WHEN N.IdNivelEnsenanza IN (2) THEN 1 WHEN N.IdNivelEnsenanza IN (4) 
                      THEN 2 WHEN N.IdNivelEnsenanza = 3 AND CL.IdCiclo = 1 THEN 1 WHEN N.IdNivelEnsenanza = 3 AND 
                      CL.IdCiclo = 2 THEN 2 ELSE 3 END AS TituloDelReporte, CASE WHEN ic.cargogenerico IN (35, 320, 405, 620) AND 
                      ic.titulovalorado = 71870 THEN '##' WHEN ic.cargogenerico IN (55, 415, 330, 7115) AND 
                      ic.titulovalorado = 46480 THEN '**' WHEN ic.asignaturagenerica IN (7115) AND ic.titulovalorado = 46480 THEN '**' ELSE '  ' END AS tit, 
                      IC.CategoriaTituloAdultos, CG.Mnemo, IC.TituloValorado, CO.TipoConvocatoria, 
                      dbo.tb_Organizaciones.Nombre + ' - ' + tb_Localidades_1.localidad AS EscuelaDeOrigen, IC.idInscripcionConvocatoria
FROM         dbo.tb_Organizaciones INNER JOIN
                      dbo.tb_Localidades tb_Localidades_1 ON dbo.tb_Organizaciones.Localidad = tb_Localidades_1.idLocalidad RIGHT OUTER JOIN
                      dbo.tb_InscripcionesConvocatorias IC INNER JOIN
                      dbo.tb_Localidades L ON IC.Localidad = L.idLocalidad INNER JOIN
                      dbo.tb_CargosGenericos CG ON CG.idCargoGenerico = IC.CargoGenerico INNER JOIN
                      dbo.tb_AsignaturasGenericas AG ON AG.idAsignaturaGenerica = IC.AsignaturaGenerica INNER JOIN
                      dbo.tb_Agentes A ON A.idAgente = IC.Agente INNER JOIN
                      dbo.tb_Convocatorias CO ON CO.idConvocatoria = IC.Convocatoria INNER JOIN
                      dbo.tb_NivelesEnsenanza N ON N.idNivelEnsenanza = IC.Nivel INNER JOIN
                      dbo.tb_ModalidadesEnsenanza m ON m.idModalidad = IC.Modalidad INNER JOIN
                      dbo.tb_Ciclos CL ON CL.IdCiclo = IC.Ciclo ON dbo.tb_Organizaciones.idOrganizaciones = IC.Organizacion LEFT OUTER JOIN
                      dbo.tb_Localidades L2 ON A.Localidad = L2.idLocalidad LEFT OUTER JOIN
                      dbo.tb_CategoriasDeTitulo C ON C.idCategoria = IC.CategoriaDeTitulo
GO
GRANT SELECT ON  [dbo].[vw_OrdenDeMerito] TO [SoloVer]
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[51] 4[26] 2[11] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1[50] 2[25] 3) )"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1 [56] 4 [18] 2))"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "tb_Organizaciones"
            Begin Extent = 
               Top = 435
               Left = 494
               Bottom = 714
               Right = 684
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tb_Localidades_1"
            Begin Extent = 
               Top = 485
               Left = 836
               Bottom = 600
               Right = 1026
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "IC"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 121
               Right = 242
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L"
            Begin Extent = 
               Top = 6
               Left = 280
               Bottom = 121
               Right = 470
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CG"
            Begin Extent = 
               Top = 12
               Left = 768
               Bottom = 127
               Right = 958
            End
            DisplayFlags = 280
            TopColumn = 4
         End
         Begin Table = "AG"
            Begin Extent = 
               Top = 14
               Left = 568
               Bottom = 129
               Right = 758
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "A"
            Begin Extent = 
               Top = 143
               Left = 638
               Bottom = 258
               Right = 840
            End
            DisplayFlags = 280
      ', 'USER', N'dbo', 'VIEW', N'vw_OrdenDeMerito', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPane2', N'      TopColumn = 0
         End
         Begin Table = "CO"
            Begin Extent = 
               Top = 138
               Left = 417
               Bottom = 253
               Right = 607
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "N"
            Begin Extent = 
               Top = 328
               Left = 122
               Bottom = 413
               Right = 312
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "m"
            Begin Extent = 
               Top = 298
               Left = 503
               Bottom = 398
               Right = 693
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CL"
            Begin Extent = 
               Top = 499
               Left = 45
               Bottom = 584
               Right = 235
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L2"
            Begin Extent = 
               Top = 291
               Left = 822
               Bottom = 453
               Right = 1012
            End
            DisplayFlags = 280
            TopColumn = 2
         End
         Begin Table = "C"
            Begin Extent = 
               Top = 430
               Left = 260
               Bottom = 545
               Right = 450
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      RowHeights = 220
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 1440
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'USER', N'dbo', 'VIEW', N'vw_OrdenDeMerito', NULL, NULL
GO
DECLARE @xp int
SELECT @xp=2
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', @xp, 'USER', N'dbo', 'VIEW', N'vw_OrdenDeMerito', NULL, NULL
GO
